package server.dao;

/**************************************************************
 *   Filename : TbProjectDAO.java
 *   Function : 
 *   Comment :
 *   History : 
 *   @version 1.0
 *   @author ����
 *************************************************************/

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import server.vo.TbProjectVO;
import server.vo.TbUserVO;
import server.vo.TbExpenseVO;


public class TbProjectDAO {

	/**
	 * ���� ��ȸ
	 * 
	 * @param conn
	 * @param u_id
	 * @return ArrayList
	 * @throws Exception
	 */
			
	public ArrayList<TbProjectVO> getPjtListAll(Connection conn) throws Exception {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			ArrayList<Object> params = new ArrayList<Object>();
			StringBuffer sql = new StringBuffer();
			sql.append("\n SELECT  ");
			sql.append("\n        pjt_code ");
			sql.append("\n       ,Pjt_NAME ");
			sql.append("\n       ,beg_date ");
			sql.append("\n       ,fin_date ");
			sql.append("\n   FROM [dbo].[TB_PROJECT]");
			
			pstmt = conn.prepareStatement(sql.toString());
			
			for (int i = 0; i < params.size(); i++) {
				pstmt.setObject(i + 1, params.get(i));
			}
			
			rs = pstmt.executeQuery();
			
			ArrayList<TbProjectVO> results = new ArrayList<TbProjectVO>();
			while (rs.next()) {
				TbProjectVO tbProject = new TbProjectVO();
				tbProject.setProject_code(rs.getString("pjt_code"));
				tbProject.setProject_name(rs.getString("pjt_name"));
				tbProject.setProject_begindate(rs.getString("beg_date"));
				tbProject.setProject_finishdate(rs.getString("fin_date"));
				results.add(tbProject);
			}
			return results;

		} catch (Exception e) {
			throw e;
		} finally {
			if (rs != null)
				rs.close();

			if (pstmt != null)
				pstmt.close();
		}
	}
	
	public List<TbProjectVO> getPjtListByUserId(Connection conn, TbUserVO paramUserVO) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<TbProjectVO> results = new ArrayList<TbProjectVO>();
		
		try {
			
			StringBuffer sql = new StringBuffer();
			sql.append("\n SELECT  ");
			sql.append("\n        pjt.pjt_code  pjt_code");
			sql.append("\n       ,Pjt_name ");
			sql.append("\n       ,beg_date ");
			sql.append("\n       ,fin_date ");
			sql.append("\n   FROM [dbo].[TB_PROJECT] pjt ,  [dbo].[TB_PROJECT_USER] pjt_user ");		
			sql.append("\n 	 WHERE pjt.pjt_code = pjt_user.pjt_code ");
			sql.append("\n 	      AND pjt_user.user_id = ? ");
			
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setString(1, paramUserVO.getUser_id());
			
			rs = pstmt.executeQuery();

			while (rs.next()) {
				TbProjectVO tbProject = new TbProjectVO();
				tbProject.setProject_code(rs.getString("pjt_code"));
				tbProject.setProject_name(rs.getString("pjt_name"));
				tbProject.setProject_begindate(rs.getString("beg_date"));
				tbProject.setProject_finishdate(rs.getString("fin_date"));
				
				results.add(tbProject);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null)
				rs.close();
			if (pstmt != null)
				pstmt.close();
		}
		return results;
	}
	
	
	/**
	 *  Project View By Project Code
	 * 
	 * @param conn
	 * @param project_code
	 * @return TbProjectVO
	 * @throws Exception
	 */
	public TbProjectVO getPjtViewByPjtCode(Connection conn, TbProjectVO paramProjectVO) throws Exception {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		TbProjectVO tbProject = null;
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("\n SELECT pjt_code, Pjt_name, beg_date , fin_date ");
			sql.append("\n 	  FROM [dbo].[TB_PROJECT] ");
			sql.append("\n  WHERE   ");
			sql.append("\n  	 pjt_code = ?   ");
			

			pstmt = conn.prepareStatement(sql.toString());

			pstmt.setString(1, paramProjectVO.getProject_code());
			rs = pstmt.executeQuery();
			
			if (rs.next()) {
				tbProject = new TbProjectVO();
				
				tbProject.setProject_code(rs.getString("pjt_code"));
				tbProject.setProject_name(rs.getString("pjt_name"));
				tbProject.setProject_begindate(rs.getString("beg_date"));
				tbProject.setProject_finishdate(rs.getString("fin_date"));
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null)
				rs.close();
			if (pstmt != null)
				pstmt.close();
		}
		return tbProject;
	}
	
	
	public List<TbExpenseVO> getAcntListByPjtCode(Connection conn, TbProjectVO paramProjectVO) throws SQLException {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<TbExpenseVO> results = new ArrayList<TbExpenseVO>();
		
		try {
			
			StringBuffer sql = new StringBuffer();
			sql.append("\n SELECT  ");
			sql.append("\n        acnt.acnt_code  acnt_code");
			sql.append("\n       ,pjt_code ");
			sql.append("\n       ,acnt_name ");
			sql.append("\n       ,acnt_amount ");
			sql.append("\n       ,acnt_balance ");
			sql.append("\n   FROM [dbo].[TB_EXPENSE_ACNT] acnt ,  [dbo].[TB_EXPENSE_BUDGET] budget ");		
			sql.append("\n 	 WHERE acnt.acnt_code = budget.acnt_code ");
			sql.append("\n 	      AND budget.pjt_code = ? ");
			
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setString(1, paramProjectVO.getProject_code());
			
			rs = pstmt.executeQuery();

			while (rs.next()) {
				TbExpenseVO tbExpense = new TbExpenseVO();
				tbExpense.setAcnt_name(rs.getString("acnt_name"));
				tbExpense.setAcnt_amount(rs.getInt("acnt_amount"));
				tbExpense.setAcnt_balance(rs.getInt("acnt_balance"));
				
				results.add(tbExpense);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (rs != null)
				rs.close();
			if (pstmt != null)
				pstmt.close();
		}
		return results;
	}

	
}